﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using sBlog.Net.Domain.Entities;
using sBlog.Net.Domain.Interfaces;

namespace sBlog.Net.Domain.Concrete
{
    public class Video : DefaultDisposable, IVideo
    {
        string strConn = ConfigurationManager.ConnectionStrings["AppDb"].ConnectionString;
        public int AddVideo(VideoEntity videoInfo)
        {
            SqlConnection dbConn = new SqlConnection(strConn);
            SqlCommand dbCmd = new SqlCommand("Video_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@VideoTitle", videoInfo.VideoTitle);
            dbCmd.Parameters.AddWithValue("@VideoName", videoInfo.VideoName);
            dbCmd.Parameters.AddWithValue("@Folder", videoInfo.Folder);
            dbCmd.Parameters.AddWithValue("@CreateDate", videoInfo.CreateDate);
            dbCmd.Parameters.AddWithValue("@UpdateDate", videoInfo.UpdateDate);
            dbCmd.Parameters.AddWithValue("@Links", videoInfo.Links);

            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }

            return 1;
        }

        public int UpdateVideo(VideoEntity videoInfo)
        {
            SqlConnection dbConn = new SqlConnection(strConn);
            SqlCommand dbCmd = new SqlCommand("Video_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", videoInfo.Id);
            dbCmd.Parameters.AddWithValue("@VideoTitle", videoInfo.VideoTitle);
            dbCmd.Parameters.AddWithValue("@VideoName", videoInfo.VideoName);
            dbCmd.Parameters.AddWithValue("@Folder", videoInfo.Folder);
            dbCmd.Parameters.AddWithValue("@UpdateDate", videoInfo.UpdateDate);
            dbCmd.Parameters.AddWithValue("@Links", videoInfo.Links);

            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }

            return 1;
        }

        public VideoEntity GetById(int Id)
        {
            VideoEntity retVal = new VideoEntity();
            SqlConnection dbConn = new SqlConnection(strConn);
            SqlCommand dbCmd = new SqlCommand("GetVideoById", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Id", Id);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal.Id = int.Parse(dr["Id"].ToString());
                    retVal.VideoTitle = dr["VideoTitle"].ToString();
                    retVal.VideoName = dr["VideoName"].ToString();
                    retVal.Folder = dr["Folder"].ToString();
                    retVal.CreateDate = Convert.ToDateTime(dr["CreateDate"].ToString());
                    retVal.UpdateDate = Convert.ToDateTime(dr["UpdateDate"].ToString());
                    retVal.Links = dr["Links"].ToString();
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }

            return retVal;
        }

        public List<VideoEntity> GetListVideoWithPage(int? pageNumber, int? pageSize, ref int totalPost)
        {
            List<VideoEntity> lst = new List<VideoEntity>();
            try
            {
                SqlDataReader rs = null;
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = @"GetListVideo";
                    cmd.Parameters.AddWithValue("@PageSize", pageSize);
                    cmd.Parameters.AddWithValue("@PageNumber", pageNumber);
                    SqlParameter para = new SqlParameter("@TotalPost", SqlDbType.Int);
                    para.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(para);
                    rs = cmd.ExecuteReader();

                    while (rs.Read())
                    {
                        VideoEntity tbl = new VideoEntity();
                        tbl.Id = int.Parse(rs["Id"].ToString());
                        tbl.VideoTitle = rs["VideoTitle"].ToString();
                        tbl.VideoName = rs["VideoName"].ToString();
                        tbl.CreateDate = Convert.ToDateTime(rs["CreateDate"].ToString());
                        tbl.UpdateDate = Convert.ToDateTime(rs["UpdateDate"].ToString());
                        tbl.Links = rs["Links"].ToString();
                        lst.Add(tbl);
                    }
                    rs.Close();
                    totalPost = int.Parse(cmd.Parameters["@TotalPost"].Value.ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lst;
        }

        ~Video()
        {
            Dispose(false);
        }
    }
}
